﻿Imports System.Data
Imports System.Data.OleDb
Imports DTO
Public Class LichGanLapDao
    Dim provider As New DataProvider

    Public Function LayDanhSachXeDuocLap() As DataTable
        Dim sql As String = "Select lg.MaLichGan, tx.TenTuyen, lg.ThoiDiemKhoiHanh, lg.MaXe"
        sql += " From LichGanLap ll, LichGan lg, TUYENXE tx Where ll.MaLichGan = lg.MaLichGan and tx.MaTuyen = lg.MaTuyen"
        Return provider.ThucThiCauTruyVan(sql)
    End Function

    Public Function LayLichGanLap(ByVal maLichGan As Long) As DataTable
        Dim sql As String = "Select * From LichGanLap Where MaLichGan = " & maLichGan
        Return provider.ThucThiCauTruyVan(sql)
    End Function


    Public Function ThemMotLichGanLap(ByVal lgLap As LichGanLapDto) As Integer
        Dim sql As String = "INSERT INTO LichGanLap(MaLichGan, ThuHai,ThuBa,ThuTu,ThuNam,ThuSau,ThuBay,ChuNhat) VALUES(?,?,?,?,?,?,?,?)"

        Dim dsParameter As New List(Of OleDbParameter)
        dsParameter.Add(New OleDbParameter("@MaLichGan", lgLap.MaLichGan))
        dsParameter.Add(New OleDbParameter("@ThuHai", lgLap.ThuHai))
        dsParameter.Add(New OleDbParameter("@ThuBa", lgLap.ThuBa))
        dsParameter.Add(New OleDbParameter("@ThuTu", lgLap.ThuTu))
        dsParameter.Add(New OleDbParameter("@ThuNam", lgLap.ThuNam))
        dsParameter.Add(New OleDbParameter("@ThuSau", lgLap.ThuSau))
        dsParameter.Add(New OleDbParameter("@ThuBay", lgLap.ThuBay))
        dsParameter.Add(New OleDbParameter("@ChuNhat", lgLap.ChuNhat))

        Return provider.ThucThiTruyVanThuong(sql, dsParameter)
    End Function

    Public Function CapNhatMotLichGanLap(ByVal lgLap As LichGanLapDto) As Integer
        Dim sql As String = "UPDATE LichGanLap SET ThuHai = ?, ThuBa = ?,ThuTu = ?,ThuNam = ?,ThuSau = ?,ThuBay = ?,ChuNhat = ?"
        sql += " WHERE MaLichGan = ?"

        Dim dsParameter As New List(Of OleDbParameter)
        dsParameter.Add(New OleDbParameter("@ThuHai", lgLap.ThuHai))
        dsParameter.Add(New OleDbParameter("@ThuBa", lgLap.ThuBa))
        dsParameter.Add(New OleDbParameter("@ThuTu", lgLap.ThuTu))
        dsParameter.Add(New OleDbParameter("@ThuNam", lgLap.ThuNam))
        dsParameter.Add(New OleDbParameter("@ThuSau", lgLap.ThuSau))
        dsParameter.Add(New OleDbParameter("@ThuBay", lgLap.ThuBay))
        dsParameter.Add(New OleDbParameter("@ChuNhat", lgLap.ChuNhat))
        dsParameter.Add(New OleDbParameter("@MaLichGan", lgLap.MaLichGan))

        Return provider.ThucThiTruyVanThuong(sql, dsParameter)
    End Function

    Public Function XoaMotLichGanLap(ByVal maLichGan) As Integer
        Dim sql As String = "DELETE FROM LichGanLap WHERE MaLichGan = " & maLichGan
        Return provider.ThucThiTruyVan(sql)
    End Function
End Class
